﻿using System.Data;
using System.Data.SqlClient;

namespace ChannelVN.Cached.Common
{
    public class SqlHelpers
    {
        public static int ExecuteNoneQuery(string connectionString, string commandText, CommandType commandType, params SqlParameter[] sqlParams)
        {
            using (var cnn = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(commandText, cnn))
                {
                    cmd.CommandType = commandType;

                    foreach (var param in sqlParams)
                    {
                        cmd.Parameters.Add(param);
                    }

                    cnn.Open();
                    var returnValue = cmd.ExecuteNonQuery();
                    cnn.Close();

                    return returnValue;
                }
            }
        }

        public static DataTable ExecuteDataTable(string connectionString, string commandText, CommandType commandType, params SqlParameter[] sqlParams)
        {
            using (var cnn = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(commandText, cnn))
                {
                    cmd.CommandType = commandType;

                    foreach (var param in sqlParams)
                    {
                        cmd.Parameters.Add(param);
                    }

                    using (var adapter = new SqlDataAdapter(cmd))
                    {
                        var dtReturnValue = new DataTable();
                        adapter.Fill(dtReturnValue);
                        return dtReturnValue;
                    }
                }
            }
        }

        public static DataTable ExecuteDataTable(string connectionString, string commandText, CommandType commandType, int startRecord, int maxRecord, params SqlParameter[] sqlParams)
        {
            using (var cnn = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(commandText, cnn))
                {
                    cmd.CommandType = commandType;

                    foreach (var param in sqlParams)
                    {
                        cmd.Parameters.Add(param);
                    }

                    using (var adapter = new SqlDataAdapter(cmd))
                    {
                        var dtReturnValue = new DataTable();
                        adapter.Fill(startRecord, maxRecord, dtReturnValue);
                        return dtReturnValue;
                    }
                }
            }
        }
    }
}
